package com.dream.xiaobo.dao.impl;

import com.dream.xiaobo.dao.UserDao;
import com.dream.xiaobo.entity.User;
import com.dream.xiaobo.utils.C3P0Utils;
import com.dream.xiaobo.utils.Constants;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @author xiaobo
 * @date 2021/8/27 - 21:41
 */
public class UserDaoImpl implements UserDao {

    //创建QueryRunner实例化对象
    private QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());

    /**
     * 检查用户是否存在的方法
     * @param username
     * @return
     * @throws SQLException
     */
    @Override
    public User selectUserByName(String username) throws SQLException {

        //根据u_name查询的SQL
        String sql = "SELECT u_id as uId , u_name as uName , u_password as uPassword" +
                ", u_sex as uSex , u_status as uStatus , u_code as uCode , u_email as uEmail " +
                ", u_role as uRole FROM user WHERE u_name = ?";

        //执行SQL
        User user = queryRunner.query(sql, new BeanHandler<User>(User.class), username);

        //返回查询到的结果
        return user;
    }

    /**
     * 添加用户信息方法
     * @param user
     * @return
     */
    @Override
    public Integer insertUser(User user) throws SQLException {

        //添加user信息的SQL
        String sql = "INSERT INTO user(u_name,u_password,u_email,u_sex,u_status,u_role,u_code) VALUES(?,?,?,?,?,?,?)";

        //执行SQL
        int insert = queryRunner.update(sql,
                user.getuName(),
                user.getuPassword(),
                user.getuEmail(),
                user.getuSex(),
                user.getuStatus(),
                user.getuRole(),
                user.getuCode());

        //返回记录条数
        return insert;
    }

    /**
     * 根据激活码查询方法
     * @param code
     * @return
     */
    @Override
    public User selectUserByCode(String code) throws SQLException {

        //根据激活码查询的SQL
        String  sql = "SELECT u_id as uId , u_name as uName , u_password as uPassword" +
                ", u_sex as uSex , u_status as uStatus , u_code as uCode , u_email as uEmail " +
                ", u_role as uRole FROM user WHERE u_code = ?";
        //执行SQL
        User user = queryRunner.query(sql, new BeanHandler<User>(User.class), code);
        //返回查询到的结果
        return user;
    }

    @Override
    public List<User> selectUserByRole() throws SQLException {

        String sql = "SELECT u_id AS uId,u_name AS uName,u_password AS uPassword," +
                "u_email AS uEmail,u_sex AS uSex,u_status AS uStatus,u_role AS uRole," +
                "u_code AS uCode FROM user WHERE u_role = 0 AND u_status = 1";

        List<User> list = queryRunner.query(sql, new BeanListHandler<>(User.class));

        return list;
    }

    @Override
    public void deleteUserByUId(String uId) throws SQLException {

        String sql = "DELETE FROM user WHERE u_id = ?";

        queryRunner.update(sql,uId);

    }

    @Override
    public List<User> selectUserByStatus() throws SQLException {

        String sql = "SELECT u_id AS uId,u_name AS uName,u_password AS uPassword," +
                "u_email AS uEmail,u_sex AS uSex,u_status AS uStatus,u_role AS uRole," +
                "u_code AS uCode FROM user WHERE u_role = 0 AND u_status = 0";

        List<User> list = queryRunner.query(sql, new BeanListHandler<>(User.class));

        return list;

    }

    /**
     * 更新激活状态的方法
     * @param uid
     * @return
     * @throws SQLException
     */
    public Integer updateByUser(Integer uid) throws SQLException {

        //根据u_id更新user表中的u_status的SQL
        String sql = "UPDATE user set u_status = ? WHERE u_id = ?";
        //执行SQL
        int row = queryRunner.update(sql,
                Constants.ACTIVE_SUCCESS,
                uid);
        //返回记录条数
        return row;
    }


}
